************************************************
***Title: aqi_clean.do
***Creators: Joelle Abramowitz, Shooshan Danagoulian, and Owen Fleming*
***Notes: This file cleans the aqi data and constructs a county-date panel.

*For questions, contact
*Owen Fleming
*hg3490@wayne.edu
************************************************


**********SETUP
cd ${path}/data/aqi
clear all
set more off


**********IMPORT AND SAVE
foreach i in 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 {
	import delimited daily_aqi_by_county_`i'.csv, clear

	if `i'==2006 {
		destring statecode, force replace
		drop if missing(statecode)
	}
	
	save aqi_`i', replace
}


**********APPEND FILES
use aqi_2006, clear
foreach i in 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 {
	append using aqi_`i'
}


**********CLEAN
*county (first, drop AK, HI, PR, and others)
drop if statecode == 2 | statecode == 15 | statecode==72 | statecode==78 | statecode==80
replace statecode = statecode * 1000
gen county = statecode+countycode
drop statecode countycode

*date
gen year = substr(date,1,4)
gen month = substr(date,6,2)
gen day = substr(date,9,2)
destring year month day, replace
drop date
generate date = mdy(month, day, year)
format date %td
drop month day year

*keeps and renames
keep county date aqi category definingparameter
rename (aqi category definingparameter) (aqi aqi_category aqi_definingparameter)


**********SAVE
save aqi, replace


**********REVERT DIRECTORY
cd ${path}







